﻿using System.Collections.Generic;
using System.Data;
using System;
using UFSoft.U8.Framework.Login.UI;
using YonYou.U8.IN.DataAccess;

namespace YonYou.U8.IN.ParameterSet
{
    public class DataService
    {
        public static string ConnString;

        public static clsLogin login;

        public static List<ParamClass> GetClass()
        {
            string cmdText = "select cClassCode,cClassName,iOrder from IN_Param_Class order by iOrder ASC";
            DataTable dataTable = new DataTable();
            List<ParamClass> list = new List<ParamClass>();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    if ((IsCompanyVer() || !(dataTable.Rows[i][0].ToString().Substring(0, 2) == "GF")) && (!IsCompanyVer() || !(dataTable.Rows[i][0].ToString().Substring(0, 2) == "GL")))
                    {
                        ParamClass paramClass = new ParamClass();
                        paramClass.ClassCode = dataTable.Rows[i][0].ToString();
                        paramClass.ClassName = dataTable.Rows[i][1].ToString();
                        paramClass.Order = Convert.ToInt32(dataTable.Rows[i][2].ToString());
                        list.Add(paramClass);
                    }
                }
            }
            return list;
        }

        public static ParamClass GetClassByClassCode(string classCode)
        {
            string cmdText = "select cClassCode,cClassName,iOrder from IN_Param_Class where cClassCode='" + classCode + "'";
            DataTable dataTable = new DataTable();
            ParamClass paramClass = new ParamClass();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    paramClass.ClassCode = dataTable.Rows[0][0].ToString();
                    paramClass.ClassName = dataTable.Rows[0][1].ToString();
                    paramClass.Order = Convert.ToInt32(dataTable.Rows[0][2].ToString());
                }
            }
            return paramClass;
        }

        public static SubSys GetSubsys(string sub_ID)
        {
            string cmdText = "select cMenu_ID,cMenu_Name from V_IN_SubSys where cMenu_Id='" + sub_ID + "'";
            SubSys subSys = new SubSys();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    subSys.Sub_ID = dataTable.Rows[0][0].ToString();
                    subSys.Sub_Name = dataTable.Rows[0][1].ToString();
                }
            }
            return subSys;
        }

        public static List<SubSys> GetAllSubsys()
        {
            string cmdText = "select cMenu_ID,cMenu_Name from V_IN_SubSys";
            List<SubSys> list = new List<SubSys>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    SubSys subSys = new SubSys();
                    subSys.Sub_ID = dataTable.Rows[i][0].ToString();
                    subSys.Sub_Name = dataTable.Rows[i][1].ToString();
                    list.Add(subSys);
                }
            }
            return list;
        }

        public static List<SubSys> GetSubsysBySuperSys(string super_ID)
        {
            string cmdText = "select cMenu_Id,cMenu_name from V_IN_SubSys where cSupMenu_ID='" + super_ID + "'";
            List<SubSys> list = new List<SubSys>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    SubSys subSys = new SubSys();
                    subSys.Sub_ID = dataTable.Rows[i][0].ToString();
                    subSys.Sub_Name = dataTable.Rows[i][1].ToString();
                    list.Add(subSys);
                }
            }
            return list;
        }

        public static List<SubSys> GetSubsysInParamsBySuperSys(string super_ID)
        {
            string cmdText = "select cMenu_Id,cMenu_name from V_IN_SubSys where cSupMenu_ID='" + super_ID + "' and cMenu_Id in (select distinct cModuleId  from IN_Param)";
            List<SubSys> list = new List<SubSys>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    SubSys subSys = new SubSys();
                    subSys.Sub_ID = dataTable.Rows[i][0].ToString();
                    subSys.Sub_Name = dataTable.Rows[i][1].ToString();
                    list.Add(subSys);
                }
            }
            return list;
        }

        public static List<SuperSys> GetSupersys()
        {
            string cmdText = "select  cMenu_Id,cMenu_name from V_IN_SupSys  order by  iOrder";
            List<SuperSys> list = new List<SuperSys>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    SuperSys superSys = new SuperSys();
                    superSys.Super_ID = dataTable.Rows[i][0].ToString();
                    superSys.Super_Name = dataTable.Rows[i][1].ToString();
                    superSys.Subsyss = GetSubsysBySuperSys(superSys.Super_ID);
                    list.Add(superSys);
                }
            }
            return list;
        }

        public static List<SuperSys> GetSupersysInParams()
        {
            string cmdText = "select  cMenu_Id,cMenu_name  from V_IN_SupSys where cMenu_Id in ( select distinct V_IN_SupSys.cMenu_Id from IN_Param left join V_IN_SubSys on IN_Param.cModuleId=V_IN_SubSys.cMenu_Id inner join V_IN_SupSys on V_IN_SupSys.cMenu_Id=V_IN_SubSys.cSupMenu_Id)";
            List<SuperSys> list = new List<SuperSys>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    SuperSys superSys = new SuperSys();
                    superSys.Super_ID = dataTable.Rows[i][0].ToString();
                    superSys.Super_Name = dataTable.Rows[i][1].ToString();
                    superSys.Subsyss = GetSubsysInParamsBySuperSys(superSys.Super_ID);
                    list.Add(superSys);
                }
            }
            return list;
        }

        public static bool UpdateParameterState(string paraID, string paraValue)
        {
            string cmdText = "update IN_Param set bCheckValue='" + paraValue + "' ,bManualChange=1 where cParamId='" + paraID + "'";
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(cmdText) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool UpdateParameterState(string paraID)
        {
            string text = " update IN_Param set bCheckValue=0 ,bManualChange=1 where cGroupCode in(select cGroupcode from IN_Param where cParamId='" + paraID + "')";
            text = text + " update IN_Param set bCheckValue=1 ,bManualChange=1 where cParamId='" + paraID + "'";
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(text) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool UpdateAllParameterState()
        {
            string cmdText = " update IN_Param set bManualChange=0";
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(cmdText) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static List<Param> GetParams(string cClassCode, string cModuleIdList)
        {
            string cmdText = "select * from IN_Param where cClassCode='" + cClassCode + "' and cModuleId in (" + cModuleIdList + ")";
            DataTable dataTable = new DataTable();
            List<Param> list = new List<Param>();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Param param = new Param();
                    param.ClassCode = dataTable.Rows[i]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[i]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[i]["cParamName"].ToString();
                    param.Order = dataTable.Rows[i]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[i]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[i]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[i]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[i]["bHide"].ToString().ToLower() == "true") ? true : false);
                    list.Add(param);
                }
            }
            return list;
        }

        public static List<Param> GetParams(string cClassCode, string cModuleIdList, string isHide)
        {
            string cmdText = "select * from IN_Param where cClassCode='" + cClassCode + "' and cModuleId in (" + cModuleIdList + ") and isnull(bHide,'0')='" + isHide + "' order by iOrder";
            DataTable dataTable = new DataTable();
            List<Param> list = new List<Param>();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Param param = new Param();
                    param.ClassCode = dataTable.Rows[i]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[i]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[i]["cParamName"].ToString();
                    param.Order = dataTable.Rows[i]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[i]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[i]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[i]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[i]["bHide"].ToString().ToLower() == "true") ? true : false);
                    list.Add(param);
                }
            }
            return list;
        }

        public static Param GetParamByParmId(string paramId)
        {
            string cmdText = "select * from IN_Param left join IN_Param_Class on IN_Param.cClassCode=IN_Param_Class.cClassCode where cParamId='" + paramId + "'";
            Param param = new Param();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    param.ClassCode = dataTable.Rows[0]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[0]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[0]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[0]["cParamName"].ToString();
                    param.Order = dataTable.Rows[0]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[0]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[0]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[0]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[0]["bHide"].ToString().ToLower() == "true") ? true : false);
                }
            }
            return param;
        }

        public static List<Param> GetParamsByClass(string cClassCode)
        {
            string cmdText = "select * from IN_Param where cClassCode='" + cClassCode + "' and bCheckValue=1";
            List<Param> list = new List<Param>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Param param = new Param();
                    param.ClassCode = dataTable.Rows[i]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[i]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[i]["cParamName"].ToString();
                    param.Order = dataTable.Rows[i]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[i]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[i]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[i]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[i]["bHide"].ToString().ToLower() == "true") ? true : false);
                    list.Add(param);
                }
            }
            return list;
        }

        public static List<Param> GetParamsByClass(string cClassCode, string moduleList)
        {
            string cmdText = "select * from IN_Param where cClassCode='" + cClassCode + "' and  cModuleId in(" + moduleList + ") order by iOrder";
            DataTable dataTable = new DataTable();
            List<Param> list = new List<Param>();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Param param = new Param();
                    param.ClassCode = dataTable.Rows[i]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[i]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[i]["cParamName"].ToString();
                    param.Order = dataTable.Rows[i]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[i]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[i]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[i]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[i]["bHide"].ToString().ToLower() == "true") ? true : false);
                    list.Add(param);
                }
            }
            return list;
        }

        public static List<Param> GetSelectedParamsByClassAndModule(string cClassCode, string moduleList)
        {
            string cmdText = "select * from IN_Param where cClassCode='" + cClassCode + "' and  cModuleId in(" + moduleList + ") and bCheckValue=1 order by iOrder";
            DataTable dataTable = new DataTable();
            List<Param> list = new List<Param>();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Param param = new Param();
                    param.ClassCode = dataTable.Rows[i]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[i]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[i]["cParamName"].ToString();
                    param.Order = dataTable.Rows[i]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[i]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[i]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[i]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[i]["bHide"].ToString().ToLower() == "true") ? true : false);
                    list.Add(param);
                }
            }
            return list;
        }

        public static List<Param> GetParamsByModule(string moduleList)
        {
            string cmdText = "select * from IN_Param where cModuleId in(" + moduleList + ")";
            List<Param> list = new List<Param>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Param param = new Param();
                    param.ClassCode = dataTable.Rows[i]["cClassCode"].ToString();
                    param.ModuleId = dataTable.Rows[i]["cModuleId"].ToString();
                    param.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    param.ParamName = dataTable.Rows[i]["cParamName"].ToString();
                    param.Order = dataTable.Rows[i]["iOrder"].ToString();
                    param.CheckValue = Convert.ToBoolean(dataTable.Rows[i]["bCheckValue"]);
                    param.Groupcode = dataTable.Rows[i]["cGroupcode"].ToString();
                    param.ManualChange = ((dataTable.Rows[i]["bManualChange"].ToString().ToLower() == "true") ? true : false);
                    param.IsHide = ((dataTable.Rows[i]["bHide"].ToString().ToLower() == "true") ? true : false);
                    list.Add(param);
                }
            }
            return list;
        }

        public static List<ParamRule> GetRulesByParamId(string cParamId)
        {
            string cmdText = "select * from IN_Param_Rule where cParamId='" + cParamId + "' order by  iOrder ASC";
            List<ParamRule> list = new List<ParamRule>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    ParamRule paramRule = new ParamRule();
                    paramRule.ParamId = dataTable.Rows[i]["cParamId"].ToString();
                    paramRule.RuleId = dataTable.Rows[i]["cRuleId"].ToString();
                    paramRule.RuleName = dataTable.Rows[i]["cRuleName"].ToString();
                    paramRule.ValueType = Convert.ToInt32(dataTable.Rows[i]["iValueType"]);
                    paramRule.Sql = dataTable.Rows[i]["cSql"].ToString();
                    paramRule.Type = Convert.ToInt32(dataTable.Rows[i]["iType"]);
                    paramRule.AssemblyClass = dataTable.Rows[i]["cAssemblyClass"].ToString();
                    paramRule.AssemblyPath = dataTable.Rows[i]["cAssemblyPath"].ToString();
                    paramRule.EntryPoint = dataTable.Rows[i]["cEntryPoint"].ToString();
                    paramRule.MenuId = dataTable.Rows[i]["cMenuId"].ToString();
                    paramRule.GuideDesc = dataTable.Rows[i]["cGuideDesc"].ToString();
                    paramRule.Order = dataTable.Rows[i]["iOrder"].ToString();
                    paramRule.ParamName = GetParamByParmId(dataTable.Rows[i]["cParamId"].ToString()).ParamName;
                    paramRule.ClassName = GetClassByClassCode(GetParamByParmId(dataTable.Rows[i]["cParamId"].ToString()).ClassCode).ClassName;
                    paramRule.AccId = login.GetLoginInfo().AccID;
                    list.Add(paramRule);
                }
            }
            return list;
        }

        public static bool InsertModule(string moduleId, string moduleName)
        {
            string cmdText = string.Format("if not exists (select * from IN_Param_Module where cModuleId='{0}') insert into IN_Param_Module(cModuleId,cModuleName)values('{0}','{1}')", moduleId, moduleName);
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(cmdText) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool IsFirstLoadModule()
        {
            string cmdText = "select cModuleId from IN_Param_Module where cModuleId='00'";
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool DeleteVirtualModule()
        {
            string cmdText = "delete from IN_Param_Module where cModuleId='00'";
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(cmdText) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool DeleteModule(string moduleId)
        {
            string cmdText = "delete from IN_Param_Module where cModuleId='" + moduleId + "'";
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(cmdText) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool DeleteAllModule()
        {
            string cmdText = "delete from IN_Param_Module ";
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                if (dbContext.NonQuery(cmdText) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static bool IsExistModule(string moduleId)
        {
            string cmdText = string.Format("select cModuleId from IN_Param_Module where cModuleId='{0}'", moduleId);
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public static List<string> GetAllModule()
        {
            string cmdText = "select cModuleId,cModuleName from IN_Param_Module";
            List<string> list = new List<string>();
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    list.Add(dataTable.Rows[i][0].ToString());
                }
            }
            return list;
        }

        public static string GetGroupName(string groupCode)
        {
            string cmdText = string.Format("select cGroupName from IN_Param_Group where cGroupCode='{0}'", groupCode);
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    return dataTable.Rows[0][0].ToString();
                }
                return "";
            }
        }

        public static bool IsCompanyVer()
        {
            string cmdText = string.Format("select cIsCompanyVer from ufsystem..UA_Account where cAcc_id='{0}' and iYear='{1}' and cIsCompanyVer='1'", login.GetLoginInfo().AccID, login.GetLoginInfo().iYear);
            DataTable dataTable = new DataTable();
            using (DbContext dbContext = new DbContext(ConnString))
            {
                dataTable = dbContext.QueryDataSet(cmdText).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
        }
    }

}